USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetCompanyProfilesByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetCompanyProfilesByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetCompanyProfilesByCompanyIds]
@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT
		Companies.CompanyId, 
		CAST(Companies.Name AS NVARCHAR(200)) AS CompanyName,
		Companies.Description AS CompanyDescription,
		Countries.Name AS Country, 
		CAST(PeerGroups.ShortName AS NVARCHAR(50)) AS ShortPeerGroupName,
		CAST(ISINs.Value AS NVARCHAR(50)) AS MainISIN, 
		CAST(Sedols.Value AS NVARCHAR(50)) AS MainSedol,
		CAST(dbo.Tickers.Value AS NVARCHAR(50)) AS MainTicker,
		Companies.LastCorporateDataUpdate
	FROM    
		dbo.Companies AS Companies INNER JOIN
		@SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = Companies.CompanyId INNER JOIN 
		dbo.Countries AS Countries ON Companies.CountryId = Countries.CountryId INNER JOIN
		dbo.Sectors AS PeerGroups ON Companies.PeerGroupSector = PeerGroups.SectorId LEFT OUTER JOIN
		dbo.Tickers ON Companies.MainTickerId = dbo.Tickers.TickerId LEFT OUTER JOIN
		dbo.Sedols ON Companies.MainSedolId = dbo.Sedols.SedolId LEFT OUTER JOIN
		dbo.ISINs ON Companies.MainISINId = dbo.ISINs.ISINId	
END

GO


